﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySkiPatrolEntity;

namespace MySkiPatrolSQL
{
    public static class EventSQL
    {
        public static string GetEvents(Event data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from " + data.DataTransaction.PatrolPrefix + "_event where startdate between '" + data.StartDate + "' and '" + data.EndDate + "' order by startdate ");
            return sql.ToString();
        }

        public static string GetPublicEvents(Event data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from " + data.DataTransaction.PatrolPrefix + "_event where startdate between '" + data.StartDate + "' and '" + data.EndDate + "' and ispublic = '1' order by startdate ");
            return sql.ToString();
        }

        public static string AddEvent(Event data)
        {
            StringBuilder sql = new StringBuilder();
            //did we provide the ID?
            if (string.IsNullOrEmpty(data.ID))
            {
                sql.Append("insert into " + data.DataTransaction.PatrolPrefix + "_event (memberid, startdate,enddate,eventtitle,location,comments,alldayevent,visibleonribbon,ispublic) values ");
                sql.Append("('" + data.OwnerID + "', ");
                sql.Append("'" + data.StartDate + "', ");
                sql.Append("'" + data.EndDate + "', ");
                sql.Append("'" + data.Title.Replace("'", "''") + "', ");
                if (!string.IsNullOrEmpty(data.Location)) { sql.Append("'" + data.Location.Replace("'", "''") + "', "); } else { sql.Append("'',"); }
                if (!string.IsNullOrEmpty(data.Comments)) { sql.Append("'" + data.Comments.Replace("'", "''") + "', "); } else { sql.Append("'',"); }
                sql.Append("'" + data.AllDayEvent + "', ");
                sql.Append("'" + data.VisibleOnRibbon + "', ");
                //sql.Append("'" + calendarEvent.NumberOfSignupSlots + "', ");
                sql.Append("'" + data.IsPublic + "') ");
            }
            else
            {
                sql.Append("insert into " + data.DataTransaction.PatrolPrefix + "_event (eventid,memberid, startdate,enddate,eventtitle,location,comments,alldayevent,visibleonribbon,ispublic) values ");
                sql.Append("('" + data.ID + "', ");
                sql.Append("'" + data.OwnerID + "', ");
                sql.Append("'" + data.StartDate + "', ");
                sql.Append("'" + data.EndDate + "', ");
                sql.Append("'" + data.Title.Replace("'", "''") + "', ");
                if (!string.IsNullOrEmpty(data.Location)) { sql.Append("'" + data.Location.Replace("'", "''") + "', "); } else { sql.Append("'',"); }
                if (!string.IsNullOrEmpty(data.Comments)) { sql.Append("'" + data.Comments.Replace("'", "''") + "', "); } else { sql.Append("'',"); }
                sql.Append("'" + data.AllDayEvent + "', ");
                sql.Append("'" + data.VisibleOnRibbon + "', ");
                //sql.Append("'" + calendarEvent.NumberOfSignupSlots + "', ");
                sql.Append("'" + data.IsPublic + "') ");
            }
            return sql.ToString();
        }

        public static string UpdateEvent(Event data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("update " + data.DataTransaction.PatrolPrefix + "_event set ");
            sql.Append("startdate = '" + data.StartDate + "', ");
            sql.Append("enddate = '" + data.EndDate + "', ");
            sql.Append("eventtitle = '" + data.Title.Replace("'", "''") + "', ");
            sql.Append("location = '" + data.Location.Replace("'", "''") + "', ");
            sql.Append("comments = '" + data.Comments.Replace("'", "''") + "', ");
            sql.Append("alldayevent = '" + data.AllDayEvent + "', ");
            sql.Append("visibleonribbon = '" + data.VisibleOnRibbon + "', ");
            sql.Append("IsPublic = '" + data.IsPublic + "' ");
            //sql.Append("displayoncalendar = '" + calendarEvent.DisplayOnCalendar + "' ");
            sql.Append("where eventid = '" + data.ID + "' ");
            return sql.ToString();
        }

        public static string DeleteEvent(Event data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("delete from " + data.DataTransaction.PatrolPrefix + "_event where eventid = '" + data.ID + "' ");
            return sql.ToString();
        }

        public static string GetEvent(Event data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("declare @memberid uniqueidentifier ");
            sql.Append("select @memberid = memberid from event where eventid = '" + data.ID + "' ");
            sql.Append("select a.*, ownername = b.firstname + ' ' + b.lastname ");
            //sql.Append("select a.*, ownername = ");
            //sql.Append("case (select displayfullname from personalization where personid = @personid) ");
            //sql.Append("    when 2 then b.firstname + ' ' + substring(b.middlename,1,1) + '. ' + b.lastname  ");
            //sql.Append("    when 3 then b.firstname + ' ' + b.middlename + ' ' + b.lastname  ");
            //sql.Append("    else  b.firstname + ' ' + b.lastname ");
            //sql.Append("end ");
            sql.Append("from " + data.DataTransaction.PatrolPrefix + "_event a ");
            sql.Append("left outer join member b on b.memberid = a.memberid ");
            sql.Append("where a.eventid = '" + data.ID + "' ");
            return sql.ToString();
        }

        //public static string GetEventRecurrence(string eventID)
        //{
        //    StringBuilder sql = new StringBuilder();
        //    sql.Append("select a.eventrecurrenceid, a.eventid, a.eventdate, b.numberofsignupslots ");
        //    sql.Append("from eventrecurrence a ");
        //    sql.Append("join [events] b on b.eventid = a.eventid ");
        //    sql.Append("where a.eventid = '" + eventID + "' ");
        //    sql.Append("order by a.eventdate ");
        //    return sql.ToString();
        //}

        //public static string GetEventSignups(EventRecurrence eventRecurrence)
        //{
        //    StringBuilder sql = new StringBuilder();
        //    sql.Append("select a.eventsignupid, a.eventrecurrenceid, a.personid, ");
        //    sql.Append("b.firstname + ' ' + b.lastname as personname ");
        //    sql.Append("from eventsignup a  ");
        //    sql.Append("join person b on b.personid = a.personid ");
        //    sql.Append("where a.eventrecurrenceid = '" + eventRecurrence.EventRecurrenceID + "'");
        //    return sql.ToString();
        //}
    }
}
